--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_PRO_FLOAT_LOAB.HQL
--    Functions : 表28：贷款余额分浮动方式分产品统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_PRO_FLOAT_LOAB PARTITION (DATA_DATE = '20180331')
SELECT  AREA.AREA_CODE_4              AS FIN_ORG_DIST
       ,T.CCY                         AS CCY
       ,T.IF_FLOAT                    AS IF_FLOAT        -- 利率是否固定
       ,T.FLOAT_DESC                  AS FLOAT_DESC      -- 利率类型描述
       ,T.BUSINESS_TYPE               AS BUSINESS_TYPE   -- 贷款产品类型
       ,T.TYPE_DESC                   AS TYPE_DESC       -- 产品类型描述
       ,SUM(T.ACTUAL_BAL)/100000000   AS ACTUAL_BAL      -- 贷款余额
       ,SUM(T.BAL_LM    )/100000000   AS BAL_LM          -- 余额上期末
       ,SUM(T.BAL_YF    )/100000000   AS BAL_YF          -- 余额年初 0101
       ,SUM(T.BAL_GROW  )/100000000   AS BAL_GROW        -- 同比增长
       ,SUM(T.WSUM_BAL  )/100000000   AS WSUM_BAL        -- 余额加权值
       ,SUM(T.WBAL_LM   )/100000000   AS WBAL_LM         -- 余额加权值上期末
       ,SUM(T.WBAL_YF   )/100000000   AS WBAL_YF         -- 余额加权值年初
       ,SUM(T.WBAL_LC   )/100000000   AS WBAL_LC         -- 余额加权值去年同期
FROM (
    -- 消费贷款
    SELECT
     YE.FIN_ORG_NO                                                                                     AS FIN_ORG_NO
    ,YE.CCY                                                                                            AS CCY
    ,YE.IF_FLOAT                                                                                       AS IF_FLOAT         -- 利率是否固定
    ,CASE WHEN YE.IF_FLOAT='RF01' THEN '固定利率'
          WHEN YE.IF_FLOAT='RF02' THEN '浮动利率'
     END                                                                                               AS FLOAT_DESC       -- 利率是否固定描述
    ,CASE -- 普通贷款
          WHEN YE.BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219') THEN 'A1'
          WHEN YE.BUSINESS_TYPE IN ('F02201','F02202','F02203') THEN 'A2'
          WHEN YE.BUSINESS_TYPE='F023' THEN 'A3'
          -- 贸易融资
          WHEN YE.BUSINESS_TYPE='F081' THEN 'B1'
          WHEN YE.BUSINESS_TYPE='F082' THEN 'B2'
          -- 融资租赁
           WHEN YE.BUSINESS_TYPE='F09' THEN 'C'
     END                                                                                              AS BUSINESS_TYPE    -- 贷款产品类型
    ,CASE -- 普通贷款
          WHEN YE.BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219') THEN '消费贷款'
          WHEN YE.BUSINESS_TYPE IN ('F02201','F02202','F02203') THEN '经营贷款'
          WHEN YE.BUSINESS_TYPE='F023' THEN '固定资产贷款'
          -- 贸易融资
          WHEN YE.BUSINESS_TYPE='F081' THEN '国际贸易融资'
          WHEN YE.BUSINESS_TYPE='F082' THEN '国内贸易融资'
          -- 融资租赁
          WHEN YE.BUSINESS_TYPE='F09' THEN '融资租赁'
     END                                                                                               AS TYPE_DESC        -- 贷款产品类型描述
    ,COALESCE(YE.ACTUAL_BAL,0)                                                                         AS ACTUAL_BAL       -- 贷款余额
    ,COALESCE(YE.BAL_LM,0)                                                                             AS BAL_LM           -- 余额上期末
    ,COALESCE(YE.BAL_YF,0)                                                                             AS BAL_YF           -- 余额年初 0101
    ,COALESCE(YE.ACTUAL_BAL,0) - COALESCE(YE.BAL_LC,0)                                                 AS BAL_GROW         -- 同比增长
    ,COALESCE(YE.WSUM_BAL,0)                                                                           AS WSUM_BAL         -- 余额加权值
    ,COALESCE(YE.WBAL_LM,0)                                                                            AS WBAL_LM          -- 余额加权值上期末
    ,COALESCE(YE.WBAL_YF,0)                                                                            AS WBAL_YF          -- 余额加权值年初
    ,COALESCE(YE.WBAL_LC,0)                                                                            AS WBAL_LC          -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219','F02201','F02202','F02203','F023','F081','F082','F09')
          AND DATA_DATE='20180331') YE

    UNION ALL

    -- 合计
    SELECT
     YE.FIN_ORG_NO                                                AS FIN_ORG_NO
    ,YE.CCY                                                       AS CCY
    ,YE.IF_FLOAT                                                  AS IF_FLOAT         -- 利率是否固定
    ,CASE WHEN YE.IF_FLOAT='RF01' THEN '固定利率'
          WHEN YE.IF_FLOAT='RF02' THEN '浮动利率'
     END                                                          AS FLOAT_DESC       -- 利率是否固定描述
    ,'Z'                                                          AS BUSINESS_TYPE    -- 贷款产品类型
    ,'合计'                                                       AS TYPE_DESC        -- 贷款产品类型描述
    ,COALESCE(YE.ACTUAL_BAL,0)                                    AS ACTUAL_BAL       -- 贷款余额
    ,COALESCE(YE.BAL_LM,0)                                        AS BAL_LM           -- 余额上期末
    ,COALESCE(YE.BAL_YF,0)                                        AS BAL_YF           -- 余额年初 0101
    ,COALESCE(YE.ACTUAL_BAL,0) - COALESCE(YE.BAL_LC,0)            AS BAL_GROW         -- 同比增长
    ,COALESCE(YE.WSUM_BAL,0)                                      AS WSUM_BAL         -- 余额加权值
    ,COALESCE(YE.WBAL_LM,0)                                       AS WBAL_LM          -- 余额加权值上期末
    ,COALESCE(YE.WBAL_YF,0)                                       AS WBAL_YF          -- 余额加权值年初
    ,COALESCE(YE.WBAL_LC,0)                                       AS WBAL_LC          -- 余额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE DATA_DATE='20180331') YE
  )T
 -- 与地区表最细级关联
 LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
 LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
 GROUP BY   AREA.AREA_CODE_4
           ,T.CCY
           ,T.IF_FLOAT
           ,T.FLOAT_DESC
           ,T.BUSINESS_TYPE
           ,T.TYPE_DESC ;